﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;

namespace SmartPlant.WinCore.Helper
{
    public static class ExcelHelper
    {
        //public static string xlsxTempOutputPath =Path.Combine( Environment.GetFolderPath(Environment.SpecialFolder.UserProfile),"DI_INTOOLS_TEMP");
        public static string xlsxTempOutputPath = AppDomain.CurrentDomain.BaseDirectory.CombinePath("\\Templates\\");
        /// <summary>
        /// xls 转为 xlsx
        /// </summary>
        public static string xls2xlsx(string xlsExcelPath, string fileName)
        {


            // ...

            using (FileStream fs = new FileStream(Path.Combine(xlsExcelPath, fileName), FileMode.Open, FileAccess.Read))
            {

                HSSFWorkbook xlsWorkbook = new HSSFWorkbook(fs);
                XSSFWorkbook xlsxWorkbook = new XSSFWorkbook();
                for (int i = 0; i < xlsWorkbook.NumberOfSheets; i++)
                {
                    //ISheet sheet = xlsWorkbook.GetSheetAt(i);
                    var oldSheet = (HSSFSheet)xlsWorkbook.GetSheetAt(i);
                    XSSFSheet newSheet = (XSSFSheet)xlsxWorkbook.CreateSheet(oldSheet.SheetName);
                    // Copy cell values from 'sheet' to 'newSheet'

                    CopySheets(oldSheet, newSheet);
                }
                var xlsxPath = Path.Combine(xlsxTempOutputPath, fileName.Replace(".xls", ".xlsx"));
                using (FileStream xlsxStream = new FileStream(xlsxPath, FileMode.Create, FileAccess.Write))
                {
                    xlsxWorkbook.Write(xlsxStream);
                    return xlsxPath;
                }
            }

        }
        #region 辅助用的

        /// <summary>
        /// copyrow
        /// </summary>
        /// <param name="source"></param>
        /// <param name="destination"></param>
        private static void CopySheets(HSSFSheet source, XSSFSheet destination)
        {
            var maxColumnNum = 0;
            var mergedRegions = new List<CellRangeAddress>();
            var styleMap = new Dictionary<int, HSSFCellStyle>();
            for (int i = source.FirstRowNum; i <= source.LastRowNum; i++)
            {
                var srcRow = (HSSFRow)source.GetRow(i);
                var destRow = (XSSFRow)destination.CreateRow(i);
                if (srcRow != null)
                {
                    CopyRow(source, destination, srcRow, destRow, mergedRegions);
                    if (srcRow.LastCellNum > maxColumnNum)
                    {
                        maxColumnNum = srcRow.LastCellNum;
                    }
                }
            }
            for (int i = 0; i <= maxColumnNum; i++)
            {
                destination.SetColumnWidth(i, source.GetColumnWidth(i));
            }
        }
        /// <summary>
        /// copycell
        /// </summary>
        /// <param name="srcSheet"></param>
        /// <param name="destSheet"></param>
        /// <param name="srcRow"></param>
        /// <param name="destRow"></param>
        /// <param name="mergedRegions"></param>
        private static void CopyRow(HSSFSheet srcSheet, XSSFSheet destSheet, HSSFRow srcRow, XSSFRow destRow, List<CellRangeAddress> mergedRegions)
        {
            destRow.Height = srcRow.Height;

            for (int j = srcRow.FirstCellNum; srcRow.LastCellNum >= 0 && j <= srcRow.LastCellNum; j++)
            {
                var oldCell = (HSSFCell)srcRow.GetCell(j);
                var newCell = (XSSFCell)destRow.GetCell(j);
                if (oldCell != null)
                {
                    if (newCell == null)
                    {
                        newCell = (XSSFCell)destRow.CreateCell(j);
                    }

                    CopyCell(oldCell, newCell);

                    var mergedRegion = GetMergedRegion(srcSheet, srcRow.RowNum,
                            (short)oldCell.ColumnIndex);

                    if (mergedRegion != null)
                    {
                        var newMergedRegion = new CellRangeAddress(mergedRegion.FirstRow,
                                mergedRegion.LastRow, mergedRegion.FirstColumn, mergedRegion.LastColumn);
                        if (IsNewMergedRegion(newMergedRegion, mergedRegions))
                        {
                            mergedRegions.Add(newMergedRegion);
                            destSheet.AddMergedRegion(newMergedRegion);
                        }
                    }
                }
            }
        }
        /// <summary>
        /// style 和 value
        /// </summary>
        /// <param name="oldCell"></param>
        /// <param name="newCell"></param>
        private static void CopyCell(HSSFCell oldCell, XSSFCell newCell)
        {
            //CopyCellStyle(oldCell, newCell);

            CopyCellValue(oldCell, newCell);
        }

        private static void CopyCellValue(HSSFCell oldCell, XSSFCell newCell)
        {
            switch (oldCell.CellType)
            {
                case CellType.String:
                    newCell.SetCellValue(oldCell.StringCellValue);
                    break;
                case CellType.Numeric:
                    newCell.SetCellValue(oldCell.NumericCellValue);
                    break;
                case CellType.Blank:
                    newCell.SetCellType(CellType.Blank);
                    break;
                case CellType.Boolean:
                    newCell.SetCellValue(oldCell.BooleanCellValue);
                    break;
                case CellType.Error:
                    newCell.SetCellErrorValue(oldCell.ErrorCellValue);
                    break;
                case CellType.Formula:
                    newCell.SetCellFormula(oldCell.CellFormula);
                    break;
                default:
                    break;
            }
        }

        private static void CopyCellStyle(HSSFCell oldCell, XSSFCell newCell)
        {
            if (oldCell.CellStyle == null) return;
            newCell.CellStyle = newCell.Sheet.Workbook.GetCellStyleAt((short)(oldCell.CellStyle.Index + 1));


        }

        private static CellRangeAddress GetMergedRegion(HSSFSheet sheet, int rowNum, short cellNum)
        {
            for (var i = 0; i < sheet.NumMergedRegions; i++)
            {
                var merged = sheet.GetMergedRegion(i);
                if (merged.IsInRange(rowNum, cellNum))
                {
                    return merged;
                }
            }
            return null;
        }

        private static bool IsNewMergedRegion(CellRangeAddress newMergedRegion, List<CellRangeAddress> mergedRegions)
        {
            return !mergedRegions.Any(r =>
            r.FirstColumn == newMergedRegion.FirstColumn &&
            r.LastColumn == newMergedRegion.LastColumn &&
            r.FirstRow == newMergedRegion.FirstRow &&
            r.LastRow == newMergedRegion.LastRow);
        }

        #endregion

        /// <summary>
        /// 根据单元格内容计算宽度。NPOI没用精确的。
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        public static int CalculateColumnWidth(ICell cell)
        { 
            int res = 10;// 对于不支持的类型或空单元格，可以设定一个默认宽度 
            // 这里可以根据实际需求调整计算列宽的方式  
            // 示例中简单地将字符串长度作为列宽  
            if (cell.CellType == CellType.String)
            {
                res = Encoding.UTF8.GetByteCount(cell.StringCellValue) + 1; // 加1作为额外的空间  
            }
            else if (cell.CellType == CellType.Numeric)
            {
                // 对于数字类型，可以根据需要格式化后计算长度  
                res = cell.ToString().Length;
            }
            // 可以添加其他类型的处理逻辑，如布尔值、日期等  


            return res * 280;
        }
        #region 拓展 

        #endregion
    }

}
